const Koa = require("koa") // 导入koa
const qs = require("qs") // 导入koa
const Router = require("koa-router") //导入koa-router
const mysql = require("mysql"); // 导入mysql，连接mysql 需要用到
const {
    copyFileSync
} = require("fs");
const app = new Koa(); // 实例化koa
const router = new Router(); // 实例化路由

app.use(async (ctx, next) => {
    ctx.set('Access-Control-Allow-Origin', '*');
    ctx.set('Access-Control-Allow-Headers', 'Content-Type, Content-Length, Authorization, Accept, X-Requested-With , yourHeaderFeild');
    ctx.set('Access-Control-Allow-Methods', 'PUT, POST, GET, DELETE, OPTIONS');
    if (ctx.method == 'OPTIONS') {
        ctx.body = 200;
    } else {
        await next();
    }
});


// mysqljs 连接 mysql数据库
let connection = mysql.createConnection({
    host: '127.0.0.1', // mysql所在的主机，本地的话就是 127.0.0.1 或者 localhost, 如果数据库在服务器上，就写服务器的ip
    user: 'root', // mysql的用户名
    password: 'root', // mysql的密码
    database: 'tieta' // 你要连接那个数据库
})

// 连接 mysql
connection.connect(err => {
    // err代表失败
    if (err) {
        console.log("数据库初始化失败");
    } else {
        console.log("数据库初始化成功");
    }
})


// 因为 mysqljs不支持 Promise方式CRUD数据
// 所以我们做一个简单的封装
function resDb(sql) {
    return new Promise((resolve, reject) => {
        connection.query(sql, (err, res) => {
            if (err) {
                reject(err)
            } else {
                resolve(res)
            }
        })
    })
}
//接收post参数
function paresPostData(ctx) {
    return new Promise((resolve, reject) => {
        try {
            let postData = ''
            ctx.req.addListener('data', (data) => {
                postData += data
            })
            ctx.req.on('end', () => {
                resolve(postData)
            })

        } catch (err) {
            reject(err)
        }
    })
}
//把对象转为sql格式
function OtoSql(par) {
    let str = ''
    for (let key in par) {
        if (key !== 'id') {
            str += key + '=' + par[key] + ','
        }
    }
    str = str.substring(0,str.length-1)
    return str
}
const getZhiYuan = "SELECT * FROM zhiyuan" // 获取所有员工信息
const deleteZhiYuan = (id) => `DELETE FROM zhiyuan WHERE id=${id}` // 删除指定ID员工信息
const getSB = "SELECT * FROM sb" // 获取所有设备信息
const insertSB = (par) => `INSERT INTO sb VALUES (${par.id},${par.name},${par.status},${par.complete},${par.lt},${par.isAc})`
const isNewsb = (id) => `select count(*) From sb where id=${id}`
const updateSb = (par) => {
    let res = `UPDATE sb SET ${OtoSql(par)} WHERE id = '${par.id}'`
    return res
}
const getMsg = "SELECT * FROM message" // 获取消息
const insertMsg = (par) => `INSERT INTO message (type, time, isread, sbId, personId) VALUES (${par.type},${par.time},${par.isread},${par.sbId},${par.personId})`
const updateMsg = (par) => {
    let res = `UPDATE message SET ${OtoSql(par)} WHERE id = '${par.id}'`
    return res
}
const getZYMsg = (par) =>{
    let res = `SELECT * FROM message WHERE personId = '${par}' `
    return res
}
const sbYMsg = (par) =>{
    let res = `SELECT * FROM message WHERE sbId = '${par}' `
    return res
}
const insertZYMsg = (par) =>{
    let res = `INSERT INTO zhiyuan (id, name, phone) VALUES (${par.id},${par.name},${par.phone})`
    return res
}
// 获取所有员工信息
router.get("/zhiyuan", async ctx => {
    ctx.body = await resDb(getZhiYuan)
})
// 删除指定ID员工信息 返回1删除成功 0删除失败
router.delete("/zhiyuan/:id", async ctx => {
    let res = await resDb(deleteZhiYuan(ctx.params.id))
    ctx.body = res.affectedRows
})
// 获取所有设备信息
router.get("/sb", async ctx => {
    ctx.body = await resDb(getSB)
})
// 设备是否存在 0不存在，1存在
router.get("/isNewsb/:id", async ctx => {
    console.log(ctx.params.id)
    let res = await resDb(isNewsb(ctx.params.id))
    console.log(res)
    ctx.body = res[0]["count(*)"]
})
// 添加设备
router.post("/sb", async ctx => {
    let obj = {
        id: -1,
        name: -1,
        status: -1,
        complete: -1,
        lt: -1,
        isAc: -1
    }
    let postData = await paresPostData(ctx)
    try {
        let res = await resDb(insertSB(Object.assign(obj, qs.parse(postData))))
        console.log('添加设备成功')
ctx.body = 1
    } catch (e) {
        console.log(e)
        console.log('添加设备失败')
ctx.body = 0

    }
})
// 更新设备信息
router.put("/sb", async ctx => {
    let postData = await paresPostData(ctx)
    console.log(qs.parse(postData))
    try {
        let res = await resDb(updateSb(qs.parse(postData)))
        ctx.body= 1
        console.log('更新设备成功！')
    } catch (e) {
        console.log(e)
        ctx.body= 0
        console.log('更新设备失败！')
    }
})
// 获取所有消息
router.get("/msg", async ctx => {
    ctx.body = await resDb(getMsg)
})
// 新增消息
router.post("/msg", async ctx => { 
    let obj = {
        type: -1,
        time: -1,
        isread: -1,
        sbId: -1,
        personId: -1
    }
    let postData = await paresPostData(ctx)
    try {
        console.log(qs.parse(postData))
        let res = await resDb(insertMsg(Object.assign(obj,qs.parse(postData))))
        ctx.body=1 
        console.log('添加消息成功')
    } catch (e) {
        console.log(e)
        console.log('添加消息失败')
        ctx.body=0
    }
})
// 更新消息信息
router.put("/msg", async ctx => {
    let postData = await paresPostData(ctx)
    console.log(qs.parse(postData))
    try {
        let res = await resDb(updateMsg(qs.parse(postData)))
        ctx.body= 1
        console.log('更新消息成功！')
    } catch (e) {
        console.log(e)
        ctx.body= 0
        console.log('更新消息失败！')
    }
})
// 获取职员消息
router.get("/zymsg/:id", async ctx => {
    let res = await resDb(getZYMsg(ctx.params.id))
    ctx.body = res
})
// 新增职员信息
router.post("/zhiyuan", async ctx => { 
    let postData = await paresPostData(ctx)
    try {
        let res = await resDb(insertZYMsg(qs.parse(postData)))
    } catch (e) {
        console.log(e)
        console.log('添加职员失败')
    }
    ctx.body = 1
})
// 获取设备消息
router.get("/sbmsg/:id", async ctx => {
    let res = await resDb(sbYMsg(ctx.params.id))
    console.log(res)
    ctx.body = res
})
// koa注册路由相关
app
    .use(router.routes())
    .use(router.allowedMethods())
    // 监听端口
    .listen(3333, () => {
        console.log("server running port:" + 3333);
    })